from db.models.user_profile import UserProfile
from db.repository.base import BaseRepository
from sqlalchemy.orm import Session
from sqlalchemy import or_, and_
from typing import Optional, List, Dict, Any
import json


class UserProfileRepository(BaseRepository[UserProfile]):
    def __init__(self, db: Session):
        super().__init__(db, UserProfile)

    def get_by_user_id(self, user_id: str) -> Optional[UserProfile]:
        """根据用户ID获取用户档案"""
        return self.get_by(user_id=user_id)


    def update_user_profile(
        self, user_id: str, update_data: Dict[str, Any]
    ) -> Optional[UserProfile]:
        """更新用户档案"""
        obj = self.get_by_user_id(user_id)
        if obj:
            return self.update(obj, update_data)
        return None

    def delete_by_user_id(self, user_id: str) -> bool:
        """根据用户ID删除用户档案"""
        obj = self.get_by_user_id(user_id)
        if obj:
            self.delete(obj)
            return True
        return False

    def get_users_by_age_range(self, min_age: int, max_age: int) -> List[UserProfile]:
        """根据年龄范围获取用户"""
        return (
            self.db.query(UserProfile)
            .filter(and_(UserProfile.age >= min_age, UserProfile.age <= max_age))
            .all()
        )

    def get_users_by_gender(self, gender: str) -> List[UserProfile]:
        """根据性别获取用户"""
        return self.db.query(UserProfile).filter(UserProfile.gender == gender).all()

    def get_users_by_region(self, region: str) -> List[UserProfile]:
        """根据地域获取用户"""
        return (
            self.db.query(UserProfile)
            .filter(UserProfile.region_cultural_background.ilike(f"%{region}%"))
            .all()
        )

    def get_users_by_occupation(self, occupation: str) -> List[UserProfile]:
        """根据职业获取用户"""
        return (
            self.db.query(UserProfile)
            .filter(UserProfile.occupation_education.ilike(f"%{occupation}%"))
            .all()
        )

    def get_users_by_emotion(self, emotion: str) -> List[UserProfile]:
        """根据当前情绪获取用户"""
        return (
            self.db.query(UserProfile)
            .filter(UserProfile.current_emotion == emotion)
            .all()
        )

    def update_emotion_state(
        self,
        user_id: str,
        current_emotion: str,
        plutchik_data: Optional[Dict] = None,
        emotional_continuity: Optional[float] = None,
    ) -> Optional[UserProfile]:
        """更新用户情绪状态"""
        update_data = {"current_emotion": current_emotion}
        if plutchik_data:
            update_data["plutchik_emotion_wheel"] = plutchik_data
        if emotional_continuity is not None:
            update_data["emotional_continuity"] = emotional_continuity

        return self.update_user_profile(user_id, update_data)

    def update_personality_traits(
        self, user_id: str, mbti_data: Dict[str, float]
    ) -> Optional[UserProfile]:
        """更新人格特征（MBTI 等）"""
        return self.update_user_profile(
            user_id, {"personality": mbti_data}
        )

    def update_interaction_preferences(
        self,
        user_id: str,
        interaction_style: str,
        usage_time_period: str,
        interaction_frequency: str,
    ) -> Optional[UserProfile]:
        """更新互动偏好"""
        update_data = {
            "interaction_style": interaction_style,
            "usage_time_period": usage_time_period,
            "interaction_frequency": interaction_frequency,
        }
        return self.update_user_profile(user_id, update_data)

    def get_users_by_interaction_frequency(self, frequency: str) -> List[UserProfile]:
        """根据互动频率获取用户"""
        return (
            self.db.query(UserProfile)
            .filter(UserProfile.interaction_frequency == frequency)
            .all()
        )

    def search_users_by_multiple_criteria(self, **criteria) -> List[UserProfile]:
        """根据多个条件搜索用户"""
        query = self.db.query(UserProfile)

        for key, value in criteria.items():
            if hasattr(UserProfile, key):
                if isinstance(value, str) and "%" in value:
                    query = query.filter(getattr(UserProfile, key).ilike(value))
                else:
                    query = query.filter(getattr(UserProfile, key) == value)

        return query.all()

    def get_user_demographics_summary(self) -> Dict[str, Any]:
        """获取用户人口统计摘要"""
        total_users = self.db.query(UserProfile).count()

        # 年龄分布
        age_groups = {
            "18-25": self.db.query(UserProfile)
            .filter(and_(UserProfile.age >= 18, UserProfile.age <= 25))
            .count(),
            "26-35": self.db.query(UserProfile)
            .filter(and_(UserProfile.age >= 26, UserProfile.age <= 35))
            .count(),
            "36-45": self.db.query(UserProfile)
            .filter(and_(UserProfile.age >= 36, UserProfile.age <= 45))
            .count(),
            "46+": self.db.query(UserProfile).filter(UserProfile.age >= 46).count(),
        }

        # 性别分布
        gender_dist = {}
        gender_results = (
            self.db.query(UserProfile.gender, self.db.func.count(UserProfile.id))
            .group_by(UserProfile.gender)
            .all()
        )
        for gender, count in gender_results:
            gender_dist[gender] = count

        # 情绪分布
        emotion_dist = {}
        emotion_results = (
            self.db.query(
                UserProfile.current_emotion, self.db.func.count(UserProfile.id)
            )
            .group_by(UserProfile.current_emotion)
            .all()
        )
        for emotion, count in emotion_results:
            emotion_dist[emotion] = count

        return {
            "total_users": total_users,
            "age_distribution": age_groups,
            "gender_distribution": gender_dist,
            "emotion_distribution": emotion_dist,
        }

    def bulk_update_user_profiles(
        self, updates: List[Dict[str, Any]]
    ) -> List[UserProfile]:
        """批量更新用户档案"""
        updated_profiles = []
        for update in updates:
            user_id = update.get("user_id")
            if user_id:
                profile = self.update_user_profile(user_id, update)
                if profile:
                    updated_profiles.append(profile)
        return updated_profiles
